package com.lagou.webwork.dao;

import com.lagou.webwork.model.ListResult;
import com.lagou.webwork.model.Student;
import com.lagou.webwork.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

/**
 * @author sjp
 * @date 2021/5/27 13:33
 * @description
 */
public class StudentDaoImp implements StudentDao {

    /**
     * 获取学生信息列表
     *
     * @param paramsMap
     * @return
     */
    @Override
    public ListResult<Student> getStudentList(List<Map<String, Object>> paramsMap, int startPosition, int currentCount) {
        try {
            //1.创建QueryRunner对象,使用Druid自动管理
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL语句
            StringBuilder sql = new StringBuilder();
            sql.append("select a.id,a.sid,a.name,a.sex,a.birthdate,a.email,a.remark,a.classid,concat(b.Grade,' - ',b.name) classname  " +
                    "from student a join class b on a.classid = b.id");

            //用于计算总数
            StringBuilder sql2 = new StringBuilder();
            sql2.append("select count(1) from student join class on student.classid = class.id ");

            //3.拼接查询条件
            List<Student> queryList = null;
            int totalCount = 0;
            if (null != paramsMap && paramsMap.size() > 0) {

                List<String> keyList = new ArrayList<>();
                List<String> valueList = new ArrayList<>();

                for (Map<String, Object> map : paramsMap) {
                    keyList.add((String) map.get("key"));
                    valueList.add((String) map.get("value"));
                }


                sql.append(" where ");
                sql2.append(" where ");
                List<Object> paramsList = new ArrayList<>();
                for (int i = 0; i < keyList.size(); i++) {
                    if (0 < i) {
                        sql.append(" and ");
                        sql2.append(" and ");
                    }
                    sql.append("a." + keyList.get(i) + " like ? ");
                    sql2.append("a." + keyList.get(i) + " like ? ");
                    paramsList.add("%" + valueList.get(i) + "%");
                }

                Object[] paramsObjectArr = paramsList.toArray();

                totalCount = queryRunner.query(sql2.toString(), new ScalarHandler<Long>(), paramsObjectArr).intValue();
                sql.append(" limit ?,? ");

                paramsList.add(startPosition);
                paramsList.add(currentCount);
                paramsObjectArr = paramsList.toArray();

                queryList = queryRunner.query(sql.toString(), new BeanListHandler<Student>(Student.class), paramsObjectArr);
            } else {
                //4.执行sql
                totalCount = queryRunner.query(sql2.toString(), new ScalarHandler<Long>()).intValue();
                sql.append(" limit ?,? ");
                queryList = queryRunner.query(sql.toString(), new BeanListHandler<Student>(Student.class), startPosition, currentCount);
            }
            return new ListResult<>(queryList, totalCount);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 根据Id获取学生对象
     *
     * @param id
     * @return
     */
    @Override
    public ListResult<Student> getStudentModelById(int id) {
        //1.创建QueryRunner对象,使用Druid自动管理
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //2.编写SQL语句
        String sql = "select a.id,a.sid,a.name,a.sex,a.birthdate,a.email,a.remark,a.classid,concat(b.Grade,' - ',b.name) classname " +
                " from student a join class b on a.classid = b.id where a.id = ?";
        try {
            //3.执行SQL语句
            Student studentModel = queryRunner.query(sql, new BeanHandler<>(Student.class), id);
            if (studentModel != null) {
                List<Student> studentList = new ArrayList<>();
                studentList.add(studentModel);
                return new ListResult<>(studentList, 1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new ListResult<>(null, 0);
    }

    /**
     * 根据学号获取学生对象
     *
     * @param sid
     * @return
     */
    @Override
    public ListResult<Student> getStudentModelBySid(String sid) {
        //1.创建QueryRunner对象,使用Druid自动管理
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //2.编写SQL语句
        String sql = "select * from student where sid = ?";
        try {
            //3.执行SQL语句
            Student studentModel = queryRunner.query(sql, new BeanHandler<>(Student.class), sid);
            if (studentModel != null) {
                List<Student> studentList = new ArrayList<>();
                studentList.add(studentModel);
                return new ListResult<>(studentList, 1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new ListResult<>(null, 0);
    }

    /**
     * 添加学生
     *
     * @param addStudent
     * @return
     */
    @Override
    public ListResult<Student> addStudent(Student addStudent) {
        //1.创建QueryRunner对象,使用Druid自动管理
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //2.编写SQL语句
        String sql = "INSERT INTO student VALUES (null, ?, ?, ?, ?, ?, ?, ?)";

        try {
            //3.执行SQL语句
            int insert = queryRunner.update(sql, addStudent.getSid(), addStudent.getName(), addStudent.getSex()
                    , addStudent.getBirthDate(), addStudent.getEmail(), addStudent.getRemark(), addStudent.getClassId());

            if (insert > 0) {
                List<Student> list = new ArrayList<>();
                list.add(addStudent);
                return new ListResult<>(list, 1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return new ListResult<>(null, 0);
    }

    /**
     * 更新学生信息
     *
     * @param updateStudent
     * @return
     */
    @Override
    public ListResult<Student> updateStudent(Student updateStudent) {
        Connection connection = null;
        try {
            //1.创建QueryRunner对象,使用Druid自动管理
            QueryRunner queryRunner = new QueryRunner();
            connection = DruidUtils.getConnection();

            connection.setAutoCommit(false);


            //2.编写SQL语句
            String sql = "update student set sid = ? , name = ? , sex = ? , birthDate = ? , Email = ? , remark = ? , classId = ? where id = ?";

            //3.执行SQL语句
            int update = queryRunner.update(connection, sql, updateStudent.getSid(), updateStudent.getName(), updateStudent.getSex()
                    , updateStudent.getBirthDate(), updateStudent.getEmail(), updateStudent.getRemark(), updateStudent.getClassId(), updateStudent.getId());

            if (update > 0) {

                //修改班级人数
                int oldClassId = getOldClassId(updateStudent.getId());
                if (oldClassId != updateStudent.getClassId()) {
                    //减去旧的
                    String sql2 = "update class set number = number-1 where id = ?";
                    queryRunner.update(connection, sql2, oldClassId);
                    String sql3 = "update class set number = number+1 where id = ?";
                    queryRunner.update(connection, sql3, updateStudent.getClassId());
                }
                connection.commit();

                List<Student> list = new ArrayList<>();
                list.add(updateStudent);
                return new ListResult<>(list, 1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return new ListResult<>(null, 0);
    }

    /**
     * 批量删除学生信息
     *
     * @param idList
     * @return
     */
    @Override
    public ListResult<Integer> deleteStudentList(List<Integer> idList) {
        try {
            //1.创建QueryRunner对象,使用Druid自动管理
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL语句
            String paramsString = Arrays.toString(idList.toArray());
            paramsString = paramsString.substring(1, paramsString.length() - 1);
            String sql = "delete from student where id in (" + paramsString + ")";

            //3.执行SQL语句
            int update = queryRunner.update(sql);
            if (update > 0) {
                return new ListResult<>(idList, 1);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return new ListResult<>(null, 0);
    }

    /**
     * 判断学号是否重复
     *
     * @param student
     * @return
     */
    @Override
    public boolean checkSidRepeat(Student student) {
        int result = 0;
        try {
            //update的情况下，旧的学号与新学号相同 直接返回false
            if (student.getId() != 0) {
                //根据传入的Id获取旧学号
                String oldSid = getSidById(student.getId());
                if (oldSid.equals(student.getSid())) {
                    return false;
                }
            }

            //insert的情况
            //1.创建QueryRunner对象,使用Druid自动管理
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL语句
            String sql = "select count(*) from student where sid = ?";
            result = queryRunner.query(sql, new ScalarHandler<Long>(), student.getSid()).intValue();
            if (result > 0) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    /**
     * 根据Id获取学号
     *
     * @param id
     * @return
     */
    private String getSidById(int id) {
        try {
            //1.创建QueryRunner对象,使用Druid自动管理
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL语句
            String sql = "select sid from student where id = ?";
            String result = queryRunner.query(sql, new ScalarHandler<String>(), id);
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 获取旧的班级Id
     *
     * @param id
     * @return
     */
    private int getOldClassId(int id) {
        try {
            //1.创建QueryRunner对象,使用Druid自动管理
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL语句
            String sql = "select classId from student where id = ?";
            Integer result = queryRunner.query(sql, new ScalarHandler<>(), id);
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

}
